package com.navi.loaddata.loadhis;

import com.navi.loaddata.core.entity.ProductHis;
import com.navi.loaddata.core.gpEntity.*;
import com.navi.loaddata.core.mysqlEntity.MBisDataD;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.utils.JacksonUtil;
import com.navi.loaddata.utils.JdbcUtil;
import com.navi.loaddata.utils.SaveEtlPoint;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.amqp.core.AmqpTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import java.net.InetAddress;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.Date;

import static java.util.stream.Collectors.toList;

@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager")
public class LoadProductHis {

    private static final Logger logger = LoggerFactory.getLogger(LoadProductHis.class);
    @Autowired
    private GreenPlumRepositoryImpl dao2;

    @Autowired
    private MySqlRepositoryImpl mysqlDao;

    @Autowired
    private AmqpTemplate rabbitTemplate;
    @Autowired
    private SaveEtlPoint saveEtlPoint;

    @Value("${etl.loadprodstart}")
    private String startTime;
    @Value("${etl.loadprodend}")
    private String endTime;


    //@Scheduled(cron = "0 0/5 * * * ?")
    //@Scheduled(fixedDelay = 1000)
    public void loadprodHis() {
        LocalDateTime etlStart = LocalDateTime.now();
        try {
            Timestamp sysTimestamp = null;
            // List<WppAshtOpeHis> a = dao2.list(WppAshtOpeHis.class, "from WppAshtOpeHis");
            List<Timestamp> list_a = dao2.createNativeQuery("SELECT max(mes_timestamp) from wpp_asht_ope_his").getResultList();
            // List<WppCshtOpeHis> c = dao2.list(WppCshtOpeHis.class, "from WppCshtOpeHis");
            List<Timestamp> list_c = dao2.createNativeQuery("SELECT max(mes_timestamp) from wpp_csht_ope_his").getResultList();
            // List<WppFshtOpeHis> f = dao2.list(WppFshtOpeHis.class, "from WppFshtOpeHis");
            List<Timestamp> list_f = dao2.createNativeQuery("SELECT max(mes_timestamp) from wpp_fsht_ope_his").getResultList();
            // List<WppAshtOpeHis> list_a = a.stream().filter(wpp -> wpp.getMesTimestamp() != null).sorted(Comparator.comparing(WppAshtOpeHis::getMesTimestamp).reversed()).collect(toList());
            // List<WppCshtOpeHis> list_c = c.stream().filter(wpp -> wpp.getMesTimestamp() != null).sorted(Comparator.comparing(WppCshtOpeHis::getMesTimestamp).reversed()).collect(toList());
            // List<WppFshtOpeHis> list_f = f.stream().filter(wpp -> wpp.getMesTimestamp() != null).sorted(Comparator.comparing(WppFshtOpeHis::getMesTimestamp).reversed()).collect(toList());
            List<Long> arrList = new ArrayList<Long>();
            if (list_a != null && list_a.get(0) != null) {
                arrList.add(list_a.get(0).getTime());
            }
            if (list_c != null && list_c.get(0) != null) {
                arrList.add(list_c.get(0).getTime());
            }
            if (list_f != null && list_f.get(0) != null) {
                arrList.add(list_f.get(0).getTime());
            }
            if (arrList.size() > 0) {
                long[] arr = new long[arrList.size()];
                for (int i = 0, j = arrList.size(); i < j; i++) {
                    arr[i] = arrList.get(i);
                }
                long temp = 0;
                for (int i = 0; i < arr.length - 1; i++) {
                    for (int j = 0; j < arr.length - 1 - i; j++) {
                        if (arr[j] > arr[j + 1]) {
                            temp = arr[j];
                            arr[j] = arr[j + 1];
                            arr[j + 1] = temp;
                        }
                    }
                }
                sysTimestamp = Timestamp.valueOf(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(arr[arr.length - 1])));

            }
            QueryRunner qr_ora = JdbcUtil.getQueryRunner_ora();
            String sql = "";
            if (sysTimestamp != null) {
                sql = "SELECT" +
                        " TrackOut.SUBFACTORYNAME AS shop_id," +
                        " TrackOut.ARRAYGLASSNAME AS TFT_GLASS_ID," +
                        " TrackOut.CFGLASSNAME AS CF_GLASS_ID," +
                        " TrackOut.PRODUCTNAME AS GLASS_ID_O," +
                        " TrackOut.LINKKEY AS LINKKEY," +
                        " TrackOut.PRODUCTSPECNAME AS PROD_ID," +
                        " TrackOut.OWNERID AS OWNER," +
                        " TrackOut.EVENTUSER AS EVT_USER," +
                        " TrackOut.INSPTARGETEQP AS UNIT_EQ," +
                        " TrackOut.PRODUCTGRADE AS grade," +
                        " TrackOut.OLDPROCESSOPERATIONNAME AS OPE_ID," +
                        " TrackOut.MACHINENAME AS EQPT_ID," +
                        " TrackOut.MACHINERECIPENAME AS RECIPE_ID," +
                        " TrackOut.OLDLOTNAME AS lot_id," +
                        " TrackOut.POSITION," +
                        " TrackOut.PRODUCTSPECVERSION AS PRODUCT_SPECVERSION," +
                        " TrackOut.CARRIERNAME AS CARRIER_NAME," +
                        " TrackOut.REWORKCOUNT AS REWORK_COUNT," +
                        " TrackOut.SYSTEMTIME AS MES_TIMESTAMP," +
                        " TrackOut.EVENTTIME AS LOGOF_TIMESTAMP" +
                        " FROM" +
                        " P1RPTADM.MES_PRODUCTHISTORY TrackOut" +
                        " WHERE" +
                        " TrackOut.EVENTNAME='TrackOut'" +
                        " AND TrackOut.SYSTEMTIME >= to_date('" + sysTimestamp.toString().substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')" +
                        " AND TrackOut.SYSTEMTIME < to_date('" + Timestamp.valueOf(LocalDateTime.now().minusMinutes(5)).toString().substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')";
            } else {
                sql = "SELECT" +
                        " TrackOut.SUBFACTORYNAME AS shop_id," +
                        " TrackOut.ARRAYGLASSNAME AS TFT_GLASS_ID," +
                        " TrackOut.CFGLASSNAME AS CF_GLASS_ID," +
                        " TrackOut.PRODUCTNAME AS GLASS_ID_O," +
                        " TrackOut.LINKKEY AS LINKKEY," +
                        " TrackOut.PRODUCTSPECNAME AS PROD_ID," +
                        " TrackOut.OWNERID AS OWNER," +
                        " TrackOut.EVENTUSER AS EVT_USER," +
                        " TrackOut.INSPTARGETEQP AS UNIT_EQ," +
                        " TrackOut.PRODUCTGRADE AS grade," +
                        " TrackOut.OLDPROCESSOPERATIONNAME AS OPE_ID," +
                        " TrackOut.MACHINENAME AS EQPT_ID," +
                        " TrackOut.MACHINERECIPENAME AS RECIPE_ID," +
                        " TrackOut.OLDLOTNAME AS lot_id," +
                        " TrackOut.POSITION," +
                        " TrackOut.PRODUCTSPECVERSION AS PRODUCT_SPECVERSION," +
                        " TrackOut.CARRIERNAME AS CARRIER_NAME," +
                        " TrackOut.REWORKCOUNT AS REWORK_COUNT," +
                        " TrackOut.SYSTEMTIME AS MES_TIMESTAMP," +
                        " TrackOut.EVENTTIME AS LOGOF_TIMESTAMP" +
                        " FROM" +
                        " P1RPTADM.MES_PRODUCTHISTORY TrackOut" +
                        " WHERE" +
                        " TrackOut.EVENTNAME='TrackOut'" +
                        "  AND TrackOut.SYSTEMTIME < to_date('" + Timestamp.valueOf(LocalDateTime.now().minusMinutes(5)).toString().substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')";
            }

            if (!("null").equals(startTime) || !("null").equals(endTime)) {
                logger.info("开始补数据时间{}",startTime);
                logger.info("结束补数据时间{}",endTime);
                sql = "SELECT" +
                        " TrackOut.SUBFACTORYNAME AS shop_id," +
                        " TrackOut.ARRAYGLASSNAME AS TFT_GLASS_ID," +
                        " TrackOut.CFGLASSNAME AS CF_GLASS_ID," +
                        " TrackOut.PRODUCTNAME AS GLASS_ID_O," +
                        " TrackOut.LINKKEY AS LINKKEY," +
                        " TrackOut.PRODUCTSPECNAME AS PROD_ID," +
                        " TrackOut.OWNERID AS OWNER," +
                        " TrackOut.EVENTUSER AS EVT_USER," +
                        " TrackOut.INSPTARGETEQP AS UNIT_EQ," +
                        " TrackOut.PRODUCTGRADE AS grade," +
                        " TrackOut.OLDPROCESSOPERATIONNAME AS OPE_ID," +
                        " TrackOut.MACHINENAME AS EQPT_ID," +
                        " TrackOut.MACHINERECIPENAME AS RECIPE_ID," +
                        " TrackOut.OLDLOTNAME AS lot_id," +
                        " TrackOut.POSITION," +
                        " TrackOut.PRODUCTSPECVERSION AS PRODUCT_SPECVERSION," +
                        " TrackOut.CARRIERNAME AS CARRIER_NAME," +
                        " TrackOut.REWORKCOUNT AS REWORK_COUNT," +
                        " TrackOut.SYSTEMTIME AS MES_TIMESTAMP," +
                        " TrackOut.EVENTTIME AS LOGOF_TIMESTAMP" +
                        " FROM" +
                        " P1RPTADM.MES_PRODUCTHISTORY TrackOut" +
                        " WHERE" +
                        " TrackOut.EVENTNAME='TrackOut'" +
                        " AND TrackOut.SYSTEMTIME >= to_date('" + startTime.substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')" +
                        " AND TrackOut.SYSTEMTIME <= to_date('" + endTime.substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')";
            }

            List<ProductHis> productHisList = qr_ora.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
            logger.info("共{}笔数据",productHisList.size());


            if (productHisList == null || productHisList.size() <= 0) return;
            List<MProdD> mProdDS = dao2.list(MProdD.class, "from MProdD");
            HashMap<String, Integer> prodMap = new HashMap<>();
            mProdDS.forEach(prod -> {
                int prodKey = prod.getProdKey();
                String prodId = prod.getProdId();
                String shopId = prod.getShopId();
                prodMap.put(prodId + "@" + shopId, prodKey);
            });
            List<MOpeD> mOpeDS = dao2.list(MOpeD.class, "from MOpeD");
            HashMap<String, Integer> opeMap = new HashMap<>();
            mOpeDS.forEach(ope -> {
                int opeKey = ope.getOpeKey();
                String opeId = ope.getOpeId();
                String shopId = ope.getShopId();
                opeMap.put(opeId + "@" + shopId, opeKey);
            });
            productHisList.forEach(productHis -> {
                if (productHis.getProd_id().contains("DUMMY")) return;
                if ("A".equalsIgnoreCase(productHis.getShop_id().substring(0, 1))) {
                    saveASht(qr_ora, productHis, prodMap, opeMap);
                } else if ("O".equalsIgnoreCase(productHis.getShop_id().substring(0, 1))) {
                    saveCSht(qr_ora, productHis, prodMap, opeMap);
                } else if ("C".equalsIgnoreCase(productHis.getShop_id().substring(0, 1))) {
                    saveFSht(qr_ora, productHis, prodMap, opeMap);
                }
            });
            saveEtlPoint.save2etlProduct(etlStart,productHisList);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void saveASht(QueryRunner qr_ora, ProductHis prodHis, HashMap<String, Integer> prodMap, HashMap<String, Integer> opeMap) {
        String shop_id = prodHis.getShop_id();
        String glass_id_o = prodHis.getGlass_id_o();
        String ope_id = prodHis.getOpe_id();
        String eqpt_id = prodHis.getEqpt_id();
        Integer rework_count = prodHis.getRework_count();
        String linkkey = prodHis.getLinkkey();
        String prod_id = prodHis.getProd_id();
        Timestamp logon_timestamp = null;
        //根据TrackOut查询TrackIn
        String sql = "SELECT" +
                " TrackIn.EVENTTIME AS LOGON_TIMESTAMP" +
                " FROM" +
                " P1RPTADM.MES_PRODUCTHISTORY TrackIn" +
                " WHERE" +
                " TrackIn.SUBFACTORYNAME = '"+shop_id+"'" +
                " AND TrackIn.PRODUCTNAME = SUBSTR('"+glass_id_o+"',1,10)" +
                " AND TrackIn.OLDPROCESSOPERATIONNAME = '"+ope_id+"'" +
                " AND TrackIn.MACHINENAME = '"+eqpt_id+"'" +
                " AND TrackIn.REWORKCOUNT = '"+rework_count+"'" +
                " AND TrackIn.LINKKEY = '"+linkkey+"'" +
                " AND TrackIn.EVENTNAME='TrackIn'" ;
        try {
            List<ProductHis> productHisList = qr_ora.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
            if (productHisList != null && productHisList.size() > 0) {
                logon_timestamp = productHisList.get(0).getLogon_timestamp();
            }
        } catch (SQLException e) {
            return;
        }



        /*if (rework_count > 0) {
            dao2.getEntityManager().createNativeQuery("update qmsprd.sor.wpp_asht_ope_his set real_flg = 'N' where glass_id = '"+glass_id+"' and ope_id = '"+ope_id+"' and rework_count < '"+rework_count+"'").executeUpdate();
        }*/


        Integer prodKey = prodMap.get(prod_id + "@" + shop_id);
        if (prodKey == null) {
            List<MProdD> mProdDS = dao2.list(MProdD.class, "from MProdD where prodId = ? and shopId = ?", prod_id, shop_id);
            if (mProdDS != null && mProdDS.size() > 0) {
                MProdD mProdD = mProdDS.get(0);
                prodKey = mProdD.getProdKey();
            }
        }
        Integer opeKey = opeMap.get(ope_id + "@" + shop_id);
        if (opeKey == null) {
            List<MOpeD> mOpeDS = dao2.list(MOpeD.class, "from MOpeD where opeId = ? and shopId = ?", ope_id, shop_id);
            if (mOpeDS != null && mOpeDS.size() > 0) {
                MOpeD mOpeD = mOpeDS.get(0);
                opeKey = mOpeD.getOpeKey();
            }
        }

        try {
            WppAshtOpeHis wppAshtOpe = new WppAshtOpeHis();
            if (prodKey != null) {
                wppAshtOpe.setProdKeyFk(prodKey);
            }
            if (opeKey != null) {
                wppAshtOpe.setOpeKeyFk(opeKey);
            }
            // wppAshtOpe.setRealFlg("Y");


            wppAshtOpe.setGlassId(glass_id_o.substring(0,10));
            wppAshtOpe.setProdId(prod_id);
            wppAshtOpe.setOwner(prodHis.getOwner());
            wppAshtOpe.setLotId(prodHis.getLot_id());
            wppAshtOpe.setGrade(prodHis.getGrade());
            wppAshtOpe.setOpeId(ope_id);
            wppAshtOpe.setLogonTimestamp(logon_timestamp);
            wppAshtOpe.setLogofTimestamp(prodHis.getLogof_timestamp());
            wppAshtOpe.setEvtUser(prodHis.getEvt_user());
            wppAshtOpe.setEqptId(eqpt_id);
            wppAshtOpe.setUnitEq(prodHis.getUnit_eq());
            wppAshtOpe.setRecipeId(prodHis.getRecipe_id());
            wppAshtOpe.setPosition(prodHis.getPosition());
            wppAshtOpe.setProductSpecversion(prodHis.getProduct_specversion());
            wppAshtOpe.setCarrierName(prodHis.getCarrier_name());
            wppAshtOpe.setReworkCount(prodHis.getRework_count());
            wppAshtOpe.setEtlTimestamp(Timestamp.valueOf(LocalDateTime.now()));
            wppAshtOpe.setEtlEventNode(InetAddress.getLocalHost().getHostAddress());
            wppAshtOpe.setMesTimestamp(prodHis.getMes_timestamp());
            wppAshtOpe.setEtlEventName(InetAddress.getLocalHost().getHostName());
            wppAshtOpe.setOpeTblName("wpp_asht_ope_his");

            //modify by LynnSea

            Timestamp trackOutTimestamp = wppAshtOpe.getLogofTimestamp();

            //得到mysql中设定的白班和夜班的开始结束时间
            final String shfName = getShfNameProc(trackOutTimestamp);

            String hql;
            final String shopId = "1";

            //从m_shf_d表中查询出来shfId和shfKey ,并赋值shfKeyFk , shfId
            hql = "from MShfD where shfName = '" + shfName + "' and shop_key =' " + shopId + "'";
            final MShfD mShfD = dao2.uniqueResult(MShfD.class, hql);
            if (mShfD != null) {
                wppAshtOpe.setShfId(mShfD.getShfId());
//                wppAshtOpe.setShfKeyFk(mShfD.getShfKey());
            }

            //判断日期归属，白班和夜班存的是同一天
            //caldDate,caldKey

            final LocalDateTime localDateTime = trackOutTimestamp.toLocalDateTime();
            LocalDate localDate = LocalDate.ofYearDay(localDateTime.getYear(), localDateTime.getDayOfYear());

            if (StringUtils.equalsIgnoreCase(shfName, "Night")) {
                if (localDateTime.getHour() < 12 ) {
                    localDate = localDate.plusDays(-1);
                }
            }
            wppAshtOpe.setCaldDate(java.sql.Date.valueOf(localDate));

//            final MCaldD mCaldD = calcCaldProc(trackOutTimestamp, shfName, dayShiftFlg ? strDayShiftStartTime : strNightShiftStartTime,
//                    dayShiftFlg ? strDayShiftEndTime : strNightShiftEndTime);
//            if (mCaldD != null) {
//                wppAshtOpe.setCaldDate(mCaldD.getCaldDate());
////                wppAshtOpe.setCaldKeyFk(mCaldD.getCaldKey());
//            }


            //判断小时，
            //并赋值给hourKeyFk,hourId

            hql = "from MHourD";
            final List<MHourD> mHourDList = dao2.find(MHourD.class, hql);
            final MHourD mHourD = calcHourProc(trackOutTimestamp, mHourDList);
            if (mHourD != null) {
//                wppAshtOpe.setHourId(mHourD.getHourNum());
                wppAshtOpe.setHourKeyFk(mHourD.getHourKey());
            }


            try {
                //FIXME:测试用，不能送出消息给RabbitMQ
                rabbitTemplate.convertAndSend("qmsExchange", "wpp_asht_ope_his", JacksonUtil.toJSONStr(wppAshtOpe));
            } catch (Exception e) {
                e.printStackTrace();
            }
            // dao2.update(wppAshtOpe);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private String getShfNameProc(Timestamp trackOutTimestamp) {
        String hql = "from MBisDataD where dataCate = ? ";
        List<MBisDataD> wktmList = mysqlDao.list(MBisDataD.class, hql, "WKTM");

        MBisDataD dayShiftWktm = wktmList.stream().filter(w -> StringUtils.equalsIgnoreCase(w.getExt1(), "1")).findFirst().get();
        MBisDataD nightShiftWktm = wktmList.stream().filter(w -> StringUtils.equalsIgnoreCase(w.getExt1(), "2")).findFirst().get();

        String strDayShiftStartTime = dayShiftWktm.getExt2();
        String strDayShiftEndTime = dayShiftWktm.getExt3();
        String strNightShiftStartTime = nightShiftWktm.getExt2();
        String strNightShiftEndTime = nightShiftWktm.getExt3();

        //判断tracktime是属于白班还是夜班
        //Timestamp trackOutTimestamp = Timestamp.valueOf(LocalDateTime.now());

        final boolean dayShiftFlg = isDayShift(trackOutTimestamp, strDayShiftStartTime, strDayShiftEndTime);
        return dayShiftFlg ? "Day" : "Night";
    }

    public void saveFSht(QueryRunner qr_ora, ProductHis prodHis, HashMap<String, Integer> prodMap, HashMap<String, Integer> opeMap) {
        String shop_id = prodHis.getShop_id();
        String glass_id_o = prodHis.getGlass_id_o();
        String ope_id = prodHis.getOpe_id();
        String eqpt_id = prodHis.getEqpt_id();
        Integer rework_count = prodHis.getRework_count();
        String linkkey = prodHis.getLinkkey();
        String prod_id = prodHis.getProd_id();
        Timestamp logon_timestamp = null;
        //根据TrackOut查询TrackIn
        String sql = "SELECT" +
                " TrackIn.EVENTTIME AS LOGON_TIMESTAMP" +
                " FROM" +
                " P1RPTADM.MES_PRODUCTHISTORY TrackIn" +
                " WHERE" +
                " TrackIn.SUBFACTORYNAME = '"+shop_id+"'" +
                " AND TrackIn.PRODUCTNAME = SUBSTR('"+glass_id_o+"',1,10)" +
                " AND TrackIn.OLDPROCESSOPERATIONNAME = '"+ope_id+"'" +
                " AND TrackIn.MACHINENAME = '"+eqpt_id+"'" +
                " AND TrackIn.REWORKCOUNT = '"+rework_count+"'" +
                " AND TrackIn.LINKKEY = '"+linkkey+"'" +
                " AND TrackIn.EVENTNAME='TrackIn'" ;
        try {
            List<ProductHis> productHisList = qr_ora.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
            if (productHisList != null && productHisList.size() > 0) {
                logon_timestamp = productHisList.get(0).getLogon_timestamp();
            }
        } catch (SQLException e) {
            return;
        }

        Integer prodKey = prodMap.get(prod_id + "@" + shop_id);
        if (prodKey == null) {
            List<MProdD> mProdDS = dao2.list(MProdD.class, "from MProdD where prodId = ? and shopId = ?", prod_id, shop_id);
            if (mProdDS != null && mProdDS.size() > 0) {
                MProdD mProdD = mProdDS.get(0);
                prodKey = mProdD.getProdKey();
            }
        }
        Integer opeKey = opeMap.get(ope_id + "@" + shop_id);
        if (opeKey == null) {
            List<MOpeD> mOpeDS = dao2.list(MOpeD.class, "from MOpeD where opeId = ? and shopId = ?", ope_id, shop_id);
            if (mOpeDS != null && mOpeDS.size() > 0) {
                MOpeD mOpeD = mOpeDS.get(0);
                opeKey = mOpeD.getOpeKey();
            }
        }
        try {
            WppFshtOpeHis wppFshtOpe = new WppFshtOpeHis();
            if (prodKey != null) {
                wppFshtOpe.setProdKeyFk(prodKey);
            }
            if (opeKey != null) {
                wppFshtOpe.setOpeKeyFk(opeKey);
            }
            // wppFshtOpe.setRealFlg("Y");
            wppFshtOpe.setGlassId(glass_id_o.substring(0,10));
            wppFshtOpe.setProdId(prod_id);
            wppFshtOpe.setOwner(prodHis.getOwner());
            wppFshtOpe.setLotId(prodHis.getLot_id());
            wppFshtOpe.setGrade(prodHis.getGrade());
            wppFshtOpe.setOpeId(ope_id);
            wppFshtOpe.setLogonTimestamp(logon_timestamp);
            wppFshtOpe.setLogofTimestamp(prodHis.getLogof_timestamp());
            wppFshtOpe.setEvtUser(prodHis.getEvt_user());
            wppFshtOpe.setEqptId(eqpt_id);
            wppFshtOpe.setUnitEq(prodHis.getUnit_eq());
            wppFshtOpe.setRecipeId(prodHis.getRecipe_id());
            wppFshtOpe.setPosition(prodHis.getPosition());
            wppFshtOpe.setProductSpecversion(prodHis.getProduct_specversion());
            wppFshtOpe.setCarrierName(prodHis.getCarrier_name());
            wppFshtOpe.setPosition(prodHis.getPosition());
            wppFshtOpe.setProductSpecversion(prodHis.getProduct_specversion());
            wppFshtOpe.setReworkCount(prodHis.getRework_count());
            wppFshtOpe.setEtlTimestamp(Timestamp.valueOf(LocalDateTime.now()));
            wppFshtOpe.setEtlEventNode(InetAddress.getLocalHost().getHostAddress());
            wppFshtOpe.setMesTimestamp(prodHis.getMes_timestamp());
            wppFshtOpe.setEtlEventName(InetAddress.getLocalHost().getHostName());
            wppFshtOpe.setOpeTblName("wpp_fsht_ope_his");

            //modify by LynnSea

            Timestamp trackOutTimestamp = wppFshtOpe.getLogofTimestamp();

            //得到mysql中设定的白班和夜班的开始结束时间
            final String shfName = getShfNameProc(trackOutTimestamp);

            String hql;
            final String shopId = "2";

            //从m_shf_d表中查询出来shfId和shfKey ,并赋值shfKeyFk , shfId
            hql = "from MShfD where shfName = '" + shfName + "' and shop_key =' " + shopId + "'";
            final MShfD mShfD = dao2.uniqueResult(MShfD.class, hql);
            if (mShfD != null) {
                wppFshtOpe.setShfId(mShfD.getShfId());
//                wppAshtOpe.setShfKeyFk(mShfD.getShfKey());
            }

            //判断日期归属，白班和夜班存的是同一天
            //caldDate,caldKey

            final LocalDateTime localDateTime = trackOutTimestamp.toLocalDateTime();
            LocalDate localDate = LocalDate.ofYearDay(localDateTime.getYear(), localDateTime.getDayOfYear());

            if (StringUtils.equalsIgnoreCase(shfName, "Night")) {
                if (localDateTime.getHour() < 12 ) {
                    localDate = localDate.plusDays(-1);
                }
            }
            wppFshtOpe.setCaldDate(java.sql.Date.valueOf(localDate));

            //判断小时，
            //并赋值给hourKeyFk,hourId

            hql = "from MHourD";
            final List<MHourD> mHourDList = dao2.find(MHourD.class, hql);
            final MHourD mHourD = calcHourProc(trackOutTimestamp, mHourDList);
            if (mHourD != null) {
                wppFshtOpe.setHourKeyFk(mHourD.getHourKey());
            }
            try {
                rabbitTemplate.convertAndSend("qmsExchange", "wpp_fsht_ope_his", JacksonUtil.toJSONStr(wppFshtOpe));
            } catch (Exception e) {
                e.printStackTrace();
            }
            //dao2.update(wppFshtOpe);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void saveCSht(QueryRunner qr_ora, ProductHis prodHis, HashMap<String, Integer> prodMap, HashMap<String, Integer> opeMap) {
        String shop_id = prodHis.getShop_id();
        String glass_id_o = prodHis.getGlass_id_o();
        String ope_id = prodHis.getOpe_id();
        String eqpt_id = prodHis.getEqpt_id();
        Integer rework_count = prodHis.getRework_count();
        String linkkey = prodHis.getLinkkey();
        String prod_id = prodHis.getProd_id();
        Timestamp logon_timestamp = null;
        //根据TrackOut查询TrackIn
        String sql = "SELECT" +
                " TrackIn.EVENTTIME AS LOGON_TIMESTAMP" +
                " FROM" +
                " P1RPTADM.MES_PRODUCTHISTORY TrackIn" +
                " WHERE" +
                " TrackIn.SUBFACTORYNAME = '"+shop_id+"'" +
                " AND TrackIn.PRODUCTNAME = SUBSTR('"+glass_id_o+"',1,10)" +
                " AND TrackIn.OLDPROCESSOPERATIONNAME = '"+ope_id+"'" +
                " AND TrackIn.MACHINENAME = '"+eqpt_id+"'" +
                " AND TrackIn.REWORKCOUNT = '"+rework_count+"'" +
                " AND TrackIn.LINKKEY = '"+linkkey+"'" +
                " AND TrackIn.EVENTNAME='TrackIn'" ;
        try {
            List<ProductHis> productHisList = qr_ora.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
            if (productHisList != null && productHisList.size() > 0) {
                logon_timestamp = productHisList.get(0).getLogon_timestamp();
            }
        } catch (SQLException e) {
            return;
        }

        Integer prodKey = prodMap.get(prod_id + "@" + shop_id);
        if (prodKey == null) {
            List<MProdD> mProdDS = dao2.list(MProdD.class, "from MProdD where prodId = ? and shopId = ?", prod_id, shop_id);
            if (mProdDS != null && mProdDS.size() > 0) {
                MProdD mProdD = mProdDS.get(0);
                prodKey = mProdD.getProdKey();
            }
        }
        Integer opeKey = opeMap.get(ope_id + "@" + shop_id);
        if (opeKey == null) {
            List<MOpeD> mOpeDS = dao2.list(MOpeD.class, "from MOpeD where opeId = ? and shopId = ?", ope_id, shop_id);
            if (mOpeDS != null && mOpeDS.size() > 0) {
                MOpeD mOpeD = mOpeDS.get(0);
                opeKey = mOpeD.getOpeKey();
            }
        }
        try {
            WppCshtOpeHis wppCshtOpe = new WppCshtOpeHis();
            if (prodKey != null) {
                wppCshtOpe.setProdKeyFk(prodKey);
            }
            if (opeKey != null) {
                wppCshtOpe.setOpeKeyFk(opeKey);
            }
            // wppCshtOpe.setRealFlg("Y");
            wppCshtOpe.setGlassId(glass_id_o.substring(0,10));
            wppCshtOpe.setProdId(prod_id);
            wppCshtOpe.setOwner(prodHis.getOwner());
            wppCshtOpe.setGrade(prodHis.getGrade());
            wppCshtOpe.setOpeId(ope_id);
            wppCshtOpe.setLotId(prodHis.getLot_id());
            wppCshtOpe.setCfGlassId(prodHis.getCf_glass_id());
            wppCshtOpe.setTftGlassId(prodHis.getTft_glass_id());
            wppCshtOpe.setLogonTimestamp(logon_timestamp);
            wppCshtOpe.setLogofTimestamp(prodHis.getLogof_timestamp());
            wppCshtOpe.setEvtUser(prodHis.getEvt_user());
            wppCshtOpe.setEqptId(eqpt_id);
            wppCshtOpe.setUnitEq(prodHis.getUnit_eq());
            wppCshtOpe.setRecipeId(prodHis.getRecipe_id());
            wppCshtOpe.setCarrierName(prodHis.getCarrier_name());
            wppCshtOpe.setPosition(prodHis.getPosition());
            wppCshtOpe.setProductSpecversion(prodHis.getProduct_specversion());
            wppCshtOpe.setReworkCount(prodHis.getRework_count());
            wppCshtOpe.setEtlTimestamp(Timestamp.valueOf(LocalDateTime.now()));
            wppCshtOpe.setEtlEventNode(InetAddress.getLocalHost().getHostAddress());
            wppCshtOpe.setMesTimestamp(prodHis.getMes_timestamp());
            wppCshtOpe.setEtlEventName(InetAddress.getLocalHost().getHostName());
            wppCshtOpe.setOpeTblName("wpp_csht_ope_his");

            //modify by LynnSea

            Timestamp trackOutTimestamp = wppCshtOpe.getLogofTimestamp();

            //得到mysql中设定的白班和夜班的开始结束时间
            final String shfName = getShfNameProc(trackOutTimestamp);

            String hql;
            final String shopId = "3";

            //从m_shf_d表中查询出来shfId和shfKey ,并赋值shfKeyFk , shfId
            hql = "from MShfD where shfName = '" + shfName + "' and shop_key =' " + shopId + "'";
            final MShfD mShfD = dao2.uniqueResult(MShfD.class, hql);
            if (mShfD != null) {
                wppCshtOpe.setShfId(mShfD.getShfId());
//                wppAshtOpe.setShfKeyFk(mShfD.getShfKey());
            }

            //判断日期归属，白班和夜班存的是同一天
            //caldDate,caldKey

            final LocalDateTime localDateTime = trackOutTimestamp.toLocalDateTime();
            LocalDate localDate = LocalDate.ofYearDay(localDateTime.getYear(), localDateTime.getDayOfYear());

            if (StringUtils.equalsIgnoreCase(shfName, "Night")) {
                if (localDateTime.getHour() < 12 ) {
                    localDate = localDate.plusDays(-1);
                }
            }
            wppCshtOpe.setCaldDate(java.sql.Date.valueOf(localDate));

            //判断小时，
            //并赋值给hourKeyFk,hourId

            hql = "from MHourD";
            final List<MHourD> mHourDList = dao2.find(MHourD.class, hql);
            final MHourD mHourD = calcHourProc(trackOutTimestamp, mHourDList);
            if (mHourD != null) {
                wppCshtOpe.setHourKeyFk(mHourD.getHourKey());
            }

            try {
                rabbitTemplate.convertAndSend("qmsExchange", "wpp_csht_ope_his", JacksonUtil.toJSONStr(wppCshtOpe));
            } catch (Exception e) {
                e.printStackTrace();
            }
            //dao2.update(wppCshtOpe);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        LocalDateTime now = LocalDateTime.now().plusHours(10);

        System.out.println(isInDateTimeRange(Timestamp.valueOf(now), "08:00:00", "20:00:00"));

    }


    //    private static String isDayShift(Timestamp trackOutTimesamp, String dayShfStartTime, String dayShfEndTime, String nightShfStartTime, String nightShfEndTime) {
    private static boolean isDayShift(Timestamp trackOutTimesamp, String dayShfStartTime, String dayShfEndTime) {

        //是否要判断夜班
        boolean dayShfFlg = isInDateTimeRange(trackOutTimesamp, dayShfStartTime, dayShfEndTime) ? true : false;

        return dayShfFlg;

    }

    /**
     * 判断某个时间是否落在时间段内
     *
     * @param startTime         时间段开始时间
     * @param endTime           时间段结束时间
     * @param trackOutTimestamp 某个时间
     * @return boolean
     */
    private static boolean isInDateTimeRange(final Timestamp trackOutTimestamp, final String startTime, final String endTime) {


        final String[] startTimes = startTime.split(":");
        final String[] endTimes = endTime.split(":");

        final LocalDateTime startDatetime = LocalDateTime.of(LocalDate.now(), LocalTime.of(Integer.valueOf(startTimes[0]), Integer.valueOf(startTimes[1]), Integer.valueOf(startTimes[2])));
        LocalDateTime endDatetime = LocalDateTime.of(LocalDate.now(), LocalTime.of(Integer.valueOf(endTimes[0]), Integer.valueOf(endTimes[1]), Integer.valueOf(endTimes[2])));

        if (endDatetime.isBefore(startDatetime)) {
            endDatetime = endDatetime.plusDays(1);
        }

        final LocalDateTime trackOutTime = LocalDateTime.ofInstant(trackOutTimestamp.toInstant(), ZoneId.systemDefault());

        return trackOutTime.isAfter(startDatetime) && trackOutTime.isBefore(endDatetime);

    }

    private static MHourD calcHourProc(final Timestamp trackOutTimestamp, List<MHourD> mHourDList) {
        return mHourDList.parallelStream().filter(mHourD -> {
            final String timeIvl = mHourD.getTimeIvl();
            final String[] timeLvls = timeIvl.split("~");

            return isInTimeRange(trackOutTimestamp, timeLvls[0], timeLvls[1]);
        }).findFirst().get();
    }

    private static boolean isInTimeRange(final Timestamp trackOutTimestamp, final String startTime, final String endTime) {

        LocalDateTime localDateTime = LocalDateTime.ofInstant(trackOutTimestamp.toInstant(), ZoneId.systemDefault());
        final LocalTime trackOutTime = localDateTime.toLocalTime();
        final String[] startTimes = startTime.split(":");
        final String[] endTimes = endTime.split(":");
        final LocalTime localStartTime = LocalTime.of(Integer.valueOf(startTimes[0]), Integer.valueOf(startTimes[1]));
        final LocalTime localtEndTime = LocalTime.of(Integer.valueOf(endTimes[0]), Integer.valueOf(endTimes[1]));
        return trackOutTime.isAfter(localStartTime) && trackOutTime.isBefore(localtEndTime);
    }

    private MCaldD calcCaldProc(final Timestamp trackOutTimestamp, String shfName, String startTime, String endTime) {
        final LocalDateTime localDateTime = trackOutTimestamp.toLocalDateTime();
        LocalDate localDate = LocalDate.ofYearDay(localDateTime.getYear(), localDateTime.getDayOfYear());

        if (StringUtils.equalsIgnoreCase(shfName, "Night")) {
            if (localDateTime.getHour() < 12 ) {
                localDate = localDate.plusDays(-1);
            }
        }
        String hql = "from MCaldD where caldDate = ?";
//        final List<MCaldD> mCaldDList = dao2.list(MCaldD.class, hql, localDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
        final List<MCaldD> mCaldDList = dao2.list(MCaldD.class, hql, localDate);

        return CollectionUtils.isEmpty(mCaldDList) ? null : mCaldDList.get(0);
    }

    private static final int SHF_DAY = 1;
    private static final int SHF_NIGHT = 2;

}
